package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.CommentDao;
import com.bilibili.dto.CommentInfo;
import com.bilibili.entity.Comment;

public class CommentDaoImpl implements CommentDao {

	@Override
	public int selectCountByVId(int videoId) {
		Connection conn = BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*) AS total FROM comment WHERE video_id = ?";
			s = conn.prepareStatement(sql);
			s.setInt(1, videoId);
			rs = s.executeQuery();
			if (rs.next()) {
				return rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, s, rs);
		}
		return 0;
	}

	public List<Comment> selectAllByVId(int videoId) {
		Connection conn = BaseDao.getConn();// 打开连接
		// 关键：使用ORDER BY create_time DESC按时间倒序排列，LIMIT 3限制返回3条
		String sql = "SELECT * FROM comment WHERE video_id = " + videoId + " ORDER BY create_time DESC LIMIT 3";
		List<Comment> list = new ArrayList<Comment>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Comment c = new Comment();
				c.setCommentId(rs.getInt("comment_id"));
				c.setVideoId(rs.getInt("video_id"));
				c.setUserId(rs.getInt("user_id"));
				c.setContent(rs.getString("content"));
				c.setCreateTime(rs.getDate("create_time"));
				c.setReplyTo(rs.getInt("reply_to"));
				c.setLikeCount(rs.getInt("like_count"));
				c.setUsername(rs.getString("username"));
				list.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<Comment> selectAllByUpId(int upId) {
		Connection conn = BaseDao.getConn();// 打开连接
		String sql = "SELECT * FROM comment WHERE up_id = " + upId + " AND status = 1";
		List<Comment> list = new ArrayList<Comment>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Comment c = new Comment();
				c.setCommentId(rs.getInt("comment_id"));
				c.setVideoId(rs.getInt("video_id"));
				c.setUserId(rs.getInt("user_id"));
				c.setContent(rs.getString("content"));
				c.setCreateTime(rs.getDate("create_time"));
				c.setReplyTo(rs.getInt("reply_to"));
				c.setLikeCount(rs.getInt("like_count"));
				c.setUsername(rs.getString("username"));
				c.setStatus(rs.getInt("status"));
				c.setUpId(rs.getInt("up_id"));
				list.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean deleteComment(int commentId) {
		String sql = "UPDATE comment SET status = 0 WHERE comment_id = ?";
		Object[] param = { commentId };
		int num = BaseDao.executeUpdate(sql, param);
		if (num == 1) {
			return true;
		}
		return false;
	}

	public int selectVideoPlayNum(int videoId) {
		// TODO Auto-generated method stub
		Connection conn = BaseDao.getConn();// 打开连接
		String sql = "SELECT count(1) as total FROM comment c1 LEFT JOIN video v2 " + "ON c1.video_id = v2.video_id "
				+ "WHERE c1.video_id = ? AND v2.status = 1";
		int row = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, videoId);
			rs = ps.executeQuery();
			if (rs.next()) {
				row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return row;
	}

	@Override
	public int selectAllCommentNum() {
		// TODO Auto-generated method stub
		Connection conn = BaseDao.getConn();// 打开连接
		String sql = "SELECT count(1) as total FROM comment";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int row = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return row;
	}

	 @Override
	    public int insertComment(Comment comment) {
	        Connection con = null;
	        PreparedStatement ps = null;
	        ResultSet rs = null;
	        int commentId = 0;
	        String sql = "INSERT INTO comment " +
	                "(video_id, user_id, username, avatar_url, content, create_time, reply_to, like_count) " +
	                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	        try {
	        	 con = BaseDao.getConn();
	             ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	             ps.setInt(1, comment.getVideoId());
	             ps.setInt(2, comment.getUserId());
	             ps.setString(3, comment.getUsername());          // 新增：用户名
	             ps.setString(4, comment.getAvatarUrl());        // 新增：用户头像
	             ps.setString(5, comment.getContent());
	             ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
	             ps.setInt(7, comment.getReplyTo() != null ? comment.getReplyTo() : 0);
	             ps.setInt(8, comment.getLikeCount() >= 0 ? comment.getLikeCount() : 0);
	            
	             int rows = ps.executeUpdate();
	             if (rows > 0) {
	                 rs = ps.getGeneratedKeys();
	                 if (rs.next()) {
	                     commentId = rs.getInt(1);
	                 }
	             }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            BaseDao.closeAll(con, ps, rs);
	        }
	        return commentId;
	    }

	    @Override
	    public List<Comment> selectCommentsByVideoId(int videoId, int offset, int limit) {
	        Connection con = null;
	        PreparedStatement ps = null;
	        ResultSet rs = null;
	        List<Comment> comments = new ArrayList<>();
	        
	        // 关联用户表获取用户名和头像
	        String sql = "SELECT " +
	                "c.comment_id, c.user_id, c.username, c.avatar_url, " +
	                "c.content, c.create_time, c.like_count, c.reply_to, " +
	                "u.username AS userName, u.avatar_url AS userAvatar " +
	                "FROM comment c " +
	                "JOIN user u ON c.user_id = u.user_id " +
	                "WHERE c.video_id = ? AND c.reply_to = 0 " +
	                "ORDER BY c.create_time DESC " +
	                "LIMIT ? OFFSET ?";;
	        
	        try {
	            con = BaseDao.getConn();
	            ps = con.prepareStatement(sql);
	            ps.setInt(1, videoId);
	            ps.setInt(2, limit);
	            ps.setInt(3, offset);
	            rs = ps.executeQuery();   
	            
	            while (rs.next()) {
	                Comment comment = new Comment();
	                comment.setCommentId(rs.getInt("comment_id"));
	                comment.setUserId(rs.getInt("user_id"));
	                comment.setUsername(rs.getString("userName"));  // 用户表的用户名
	                comment.setAvatarUrl(rs.getString("avatar_url"));  // 用户表的头像
	                comment.setContent(rs.getString("content"));
	                comment.setCreateTime(rs.getTimestamp("create_time"));
	                comment.setLikeCount(rs.getInt("like_count"));
	                comment.setReplyTo(rs.getInt("reply_to"));  // 回复目标ID
	                comments.add(comment);
	           }   
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            BaseDao.closeAll(con, ps, rs);
	        }
	        return comments;
	    }

	    @Override
	    public List<Comment> selectRepliesByParentId(int parentId) {
	        Connection con = null;
	        PreparedStatement ps = null;
	        ResultSet rs = null;
	        List<Comment> replies = new ArrayList<>();
	        String sql = "SELECT * FROM comment WHERE reply_to = ? ORDER BY create_time ASC";
	        
	        try {
	            con = BaseDao.getConn();
	            ps = con.prepareStatement(sql);
	            ps.setInt(1, parentId);
	            rs = ps.executeQuery();
	            
	            while (rs.next()) {
	                Comment reply = mapResultSetToComment(rs);
	                replies.add(reply);
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            BaseDao.closeAll(con, ps, rs);
	        }
	        return replies;
	    }

	    @Override
	    public Comment selectCommentById(int commentId) {
	        Connection con = null;
	        PreparedStatement ps = null;
	        ResultSet rs = null;
	        Comment comment = null;
	        String sql = "SELECT * FROM comment WHERE comment_id = ?";
	        
	        try {
	            con = BaseDao.getConn();
	            ps = con.prepareStatement(sql);
	            ps.setInt(1, commentId);
	            rs = ps.executeQuery();
	            
	            if (rs.next()) {
	                comment = mapResultSetToComment(rs);
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            BaseDao.closeAll(con, ps, rs);
	        }
	        return comment;
	    }

	    @Override
	    public int updateCommentLikeCount(int commentId, int likeCount) {
	        Connection con = null;
	        PreparedStatement ps = null;
	        int rows = 0;
	        String sql = "UPDATE comment SET like_count = ? WHERE comment_id = ?";
	        
	        try {
	            con = BaseDao.getConn();
	            ps = con.prepareStatement(sql);
	            ps.setInt(1, likeCount);
	            ps.setInt(2, commentId);
	            rows = ps.executeUpdate();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            BaseDao.closeAll(con, ps, null);
	        }
	        return rows;
	    }
	    
	    // 结果集映射为Comment对象
	    private Comment mapResultSetToComment(ResultSet rs) throws SQLException {
	        Comment comment = new Comment();
	        comment.setCommentId(rs.getInt("comment_id"));
	        comment.setUserId(rs.getInt("user_id"));
	        comment.setUsername(rs.getString("userName"));
	        comment.setAvatarUrl(rs.getString("avatar_url"));
	        comment.setContent(rs.getString("content"));
	        comment.setCreateTime(rs.getTimestamp("create_time"));
	        comment.setLikeCount(rs.getInt("like_count"));
	        comment.setReplyTo(rs.getInt("reply_to"));
	        return comment;
	    }
	    
	    
	    @Override
		public List<CommentInfo> selectAllCommentInfoB(CommentInfo ci, int pageNum, int pageSize) {
			// TODO Auto-generated method stub
			Connection conn=BaseDao.getConn();//打开连接
			List<Object> queryList = new ArrayList<Object>();
			String sql = "SELECT * FROM comment c1 LEFT JOIN user u2 " + 
					"ON c1.user_id = u2.user_id " + 
					"LEFT JOIN video v3 " + 
					"ON c1.video_id = v3.video_id where 1=1 ";
			if(ci.getState() != -1) {
				sql+="AND c1.state = ? ";
				queryList.add(ci.getState());
			}
			if(ci.getUserId() != -1) {
				sql+="AND u2.user_id = ? ";
				queryList.add(ci.getUserId());
			}
			if(ci.getVideoTitle() != null && !"".equals(ci.getVideoTitle())) {
				sql+="AND v3.title like concat('%',?,'%') ";
				queryList.add(ci.getVideoTitle());
			}
			sql+="LIMIT ?,?";
			List<CommentInfo> list = new ArrayList<CommentInfo>();
			PreparedStatement ps= null;
			ResultSet rs=null;
			try {
				ps= conn.prepareStatement(sql);
				for(int i = 0;i<queryList.size();i++) {
					ps.setObject(i+1, queryList.get(i));
				}
				ps.setInt(queryList.size()+1, (pageNum-1)*pageSize);
				ps.setInt(queryList.size()+2, pageSize);
				rs = ps.executeQuery();			
				while(rs.next()) {
				    CommentInfo CI = new CommentInfo();
			        CI.setCommentId(rs.getInt("comment_id"));
			        CI.setVideoId(rs.getInt("video_id"));
			        CI.setUserId(rs.getInt("user_id"));
			        CI.setContent(rs.getString("content"));
			        CI.setCreateTime(rs.getTimestamp("create_time"));
			        CI.setState(rs.getInt("state"));
			        CI.setUserName(rs.getString("username"));
			        CI.setAvatarUrl(rs.getString("avatar_url"));
			        CI.setVideoTitle(rs.getString("title"));
			        list.add(CI);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(conn, ps,rs);
			}
			return list;
		}

		@Override
		public int selectAllCommentInfoBNum(CommentInfo ci) {
			// TODO Auto-generated method stub
			Connection conn=BaseDao.getConn();//打开连接
			List<Object> queryList = new ArrayList<Object>();
			String sql = "SELECT count(1) as total FROM comment c1 LEFT JOIN user u2 " + 
					"ON c1.user_id = u2.user_id " + 
					"LEFT JOIN video v3 " + 
					"ON c1.video_id = v3.video_id where 1=1 ";
			if(ci.getState() != -1) {
				sql+="AND c1.state = ? ";
				queryList.add(ci.getState());
			}
			if(ci.getUserId() != -1) {
				sql+="AND u2.user_id = ? ";
				queryList.add(ci.getUserId());
			}
			if(ci.getVideoTitle() != null && !"".equals(ci.getVideoTitle())) {
				sql+="AND v3.title like concat('%',?,'%') ";
				queryList.add(ci.getVideoTitle());
			}
			PreparedStatement ps= null;
			ResultSet rs=null;
			int row = 0;
			try {
				ps= conn.prepareStatement(sql);
				for(int i = 0;i<queryList.size();i++) {
					ps.setObject(i+1, queryList.get(i));
				}
				rs = ps.executeQuery();			
				if(rs.next()) {
				    row = rs.getInt("total");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(conn, ps,rs);
			}
			return row;
		}

		@Override
		public int deleteCommentInfoByCId(Comment c) {
			// TODO Auto-generated method stub
			String sql = "update comment set state = ? where comment_id = ?";
			Object[] param = { c.getState(),c.getCommentId() };
			return BaseDao.executeUpdate(sql, param);
		}
}
